package com.it2297.util;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import javax.sql.DataSource;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

/**
 * Create MySQL connection spooler. 
 * A connection spooler maintain connection to database so that system takes less time to execute simple query.
 * It "borrow" available connection from pool. If there's no available connection, it will establish a new one.
 * When the transaction is completed, it "return" current connection to the pool and set it under INACTIVE state.
 * <p>
 * Primarily use for optimizing performance of remote database connection. 
 * Use getInstance() to instantiate object.
 * This is a singleton class and cannot be instantiated using constructor.
 *  
 * @author kfwong
 *
 */
public final class MySQLConnection {
	private static MySQLConnection mySQLConnection = null;
	
	@SuppressWarnings("rawtypes")
	private static GenericObjectPool connectionPool = null;

	public static final String DRIVER = "com.mysql.jdbc.Driver";
	
	// Uncomment to connect local database
	
	public static final String URL = "jdbc:mysql://localhost:3306";
	public static final String USERNAME = "root";
	public static final String PASSWORD = "root";
	
	// Uncomment to connect remote database
	/*
	public static final String URL = "jdbc:mysql://144.76.19.105:3306";
	public static final String USERNAME = "whitekoffee";
	public static final String PASSWORD = "eatsh!t";
	*/
	
	private static DataSource dataSource;
	private Connection connection;

	/**
	 * Set to private to prevent illegal instantiation.
	 */
	private MySQLConnection() {}
	
	/**
	 * Create an instance of <code>MySQLConnection</code> object.
	 * If it's already exist, this method simply return the existing object.
	 * 
	 * @return MySQLConnection object - connection object use for connecting database.
	 */
	public static MySQLConnection getInstance(){
		if(mySQLConnection==null){
			dataSource = getDataSource();
			mySQLConnection = new MySQLConnection();
		}
		
		return mySQLConnection;
	}

	/**
	 * Attempt to establish connection to the spooler. If there's no available connection, this method create a new connection in the pool. 
	 * @throws SQLException - If SQL access error occurs.
	 */
	public void open() throws SQLException {
		connection = dataSource.getConnection();
	}

	/**
	 * To execute query that return <code>ResultSet</code> such as SELECT statement.
	 * @param sql - SQL query to be executed
	 * @return
	 */
	public ResultSet executeQuery(String sql) {
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		} 
		return resultSet;
	}

	/**
	 * Execute query that doesn't return any result, such as INSERT, UPDATE, DELETE statement.
	 * @param sql - SQL query to be executed
	 */
	public void executeUpdate(String sql) {
		Statement statement = null;
		try {
			statement = connection.createStatement();
			statement.executeUpdate(sql);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Return current connection to the pool.
	 */
	public void close() {
		try {
			connection.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	@SuppressWarnings("rawtypes")
	private static DataSource getDataSource() {
		try {
			Class.forName(DRIVER).newInstance();
		} catch (Exception ex) {
			System.out.println("Driver not found!");
		}

		connectionPool = new GenericObjectPool();
		connectionPool.setMaxActive(10);

		ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(URL, USERNAME, PASSWORD);

		@SuppressWarnings("unused")
		PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, connectionPool, null, null, false, true);

		return new PoolingDataSource(connectionPool);
	}
	
	public Connection getConnection(){
		return connection;
	}

	@SuppressWarnings("rawtypes")
	private GenericObjectPool getConnectionPool() {
		return connectionPool;
	}

	/**
	 * Return the status of current spooler.
	 * <p>
	 * MAX - Maximum connections allowed.
	 * <br>
	 * ACTIVE - Connections that are currently in use.
	 * <br>
	 * INACTIVE - Connections that are available to use. 
	 * 
	 * @return current status of the spooler.
	 */
	public String getStatus() {
		return "Max   : " + getConnectionPool().getMaxActive() + "; " + "Active: " + getConnectionPool().getNumActive() + "; " + "Idle  : " + getConnectionPool().getNumIdle();
	}
}
